-- @owner: @nanyang12
-- @date: 2025/3/25
-- @testpoint:GMS_XMLGEN.CLOSECONTEXT函数与其他函数一起使用
@conn PrimaryDbAmode;
--前置条件：
drop extension if exists gms_xmlgen cascade;
create extension gms_xmlgen;
drop extension if exists gms_output cascade;
create extension gms_output;
--step1:建表
drop table if exists t_close0007 cascade;
CREATE TABLE t_close0007 (
 employee_id NUMBER PRIMARY KEY,
 first_name VARCHAR2(50),
 last_name VARCHAR2(50),
 email VARCHAR2(100),
 hire_date DATE
);
insert into t_close0007 values(1,'TOM','JO','545644@qq.com','2024-12-31');
insert into t_close0007 values(2,'KILLY''s"<>','GHU','545644@qq.com','2024-12-31');
insert into t_close0007 values(3,'KILLY''s"<>','',null,'2024-12-31');

--step2:多个函数一起使用，成功
DECLARE
 l_ctx gms_XMLGEN.ctxHandle;
 l_xml CLOB;
 l_xmltype XMLTYPE;
 l_num_rows_processed NUMBER;
BEGIN

 l_ctx := gms_XMLGEN.newContext('SELECT * FROM t_close0007');


 gms_XMLGEN.setNullHandling(l_ctx, 1);


 gms_XMLGEN.setConvertSpecialChars(l_ctx, true);


 gms_XMLGEN.setMaxRows(l_ctx, 10);


 gms_XMLGEN.setSkipRows(l_ctx, 1);


 gms_XMLGEN.setRowTag(l_ctx, 'employee');
 gms_XMLGEN.setRowsetTag(l_ctx, 'HELLO');


 l_xml := gms_XMLGEN.getXML(l_ctx);


 l_num_rows_processed := gms_XMLGEN.getNumRowsProcessed(l_ctx);


 gms_OUTPUT.put_line(l_xml);
 gms_OUTPUT.put_line(l_num_rows_processed);

 gms_XMLGEN.closeContext(l_ctx);

END;
/
--step3:清理环境
drop table if exists t_close0007 cascade;
drop extension if exists gms_xmlgen cascade;